package com.xs.tool.dao;

import com.xs.tool.model.ToolTest;
import java.util.Date;
import java.util.List;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

/**
 *  JpaRepository
 * 该接口继承了PagingAndSortingRepository和QueryByExampleExecutor接口，
 * 相比CrudRepository接口多了分页和排序功能，
 * 因此JpaRepository功能更加全面，
 * 如果你不需要分页和排序功能，
 * 使用CrudRepository即可。这里我们使用JpaRepository，其定义如下：
 */
public interface ToolTestDao extends JpaRepository<ToolTest, Integer> {
  /******************************create***************************************/
  @Override
  ToolTest save(ToolTest toolTest);
  /******************************read***************************************/
  /**
   * Using sort
   * @param lastName
   * @param sort
   * @return
   */
  @Query("select u from ToolTest u where u.lastName like ?1%")
  List<ToolTest> findByAndSort(String lastName, Sort sort);

  /**
   * Query creation
   * this translates into the following query:
   * select u from ToolTest u where u.id_card = ?1
   */
  ToolTest findByIdCard(String idCard);

  /**
   * Native Queries
   * The @Query annotation allows for running native queries by setting the nativeQuery flag to true
   * @param idCard
   * @return
   */
  @Query(value = "select * from ToolTest where id_card = ?1", nativeQuery = true)
  ToolTest findByIdCard2(String idCard);

  /**
   * Using @Query
   * @param idCard
   * @return
   */
  @Query("select u from ToolTest u where u.idCard = ?1")
  ToolTest findByIdCard3(String idCard);

  /**
   * Declare native count queries for pagination at the query method by using @Query
   * @param lastName
   * @param pageable
   * @return
   */
  @Query(value = "SELECT * FROM ToolTest WHERE last_name = ?1",
      countQuery = "SELECT count(*) FROM ToolTest WHERE last_name = ?1",
      nativeQuery = true)
  Page<ToolTest> findByLastNameWithPageable(String lastName, Pageable pageable);

  /**
   * Using Named Parameters
   * @param firstName
   * @param lastName
   * @return
   */
  @Query("select u from ToolTest u where u.firstName = :firstName or u.lastName = :lastName")
  List<ToolTest> findByFirstNameOrLastName(@Param("firstName") String firstName,
      @Param("lastName") String lastName);

  /**
   * findByDateOfCreateDate
   * @param date
   * @return
   */
  List<ToolTest> findByCreateDate(@Param("date") Date date);

  /**
   * find all ToolTest
   * @param sort
   * @return
   */
  @Override
  List<ToolTest> findAll(Sort sort);
  /****************************update*****************************************/
  /**
   * update a usr by Modifying Queries
   * @param firstName
   * @param idCard
   * @return
   */
  @Modifying
  @Query("update ToolTest u set u.firstName = ?1 where u.idCard = ?2")
  int updateToolTest(String firstName, String idCard);
  /****************************delete*****************************************/
  /**
   * delete a toolTest by idCard
   * @param idCard
   */
  void deleteByIdCard(String idCard);

  /**
   * Using a derived delete query
   * @param idCard
   */
  @Modifying
  @Query("delete from ToolTest where id_card = ?1")
  void deleteByIdCard2(String idCard);

  /**
   * delete a toolTest by id
   * @param id
   */
  @Override
  @Modifying
  @Query(value = "delete from ToolTest where id = ?1", nativeQuery = true)
  void deleteById(Integer id);

}
